<?php
include dirname(__FILE__) . "/PHPExcel.php";
class ExportExcel
{

    protected $centerStyle = array(
        'font'      => array(
            'bold' => true,
        ),
        'alignment' => array(
            'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER,

        ),
    );

    protected $borderStyle = array(
        'borders' => array(
            'top' => array(
                'style' => \PHPExcel_Style_Border::BORDER_THIN,
            ),
        ));
    protected function column_str($key)
    {
        $array = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ', 'BA', 'BB', 'BC', 'BD', 'BE', 'BF', 'BG', 'BH', 'BI', 'BJ', 'BK', 'BL', 'BM', 'BN', 'BO', 'BP', 'BQ', 'BR', 'BS', 'BT', 'BU', 'BV', 'BW', 'BX', 'BY', 'BZ', 'CA', 'CB', 'CC', 'CD', 'CE', 'CF', 'CG', 'CH', 'CI', 'CJ', 'CK', 'CL', 'CM', 'CN', 'CO', 'CP', 'CQ', 'CR', 'CS', 'CT', 'CU', 'CV', 'CW', 'CX', 'CY', 'CZ', 'DA', 'DB', 'DC', 'DD', 'DE', 'DF', 'DG', 'DH', 'DI', 'DJ', 'DK', 'DL', 'DM', 'DN', 'DO', 'DP', 'DQ', 'DR', 'DS', 'DT', 'DU', 'DV', 'DW', 'DX', 'DY', 'DZ', 'EA', 'EB', 'EC', 'ED', 'EE', 'EF', 'EG', 'EH', 'EI', 'EJ', 'EK', 'EL', 'EM', 'EN', 'EO', 'EP', 'EQ', 'ER', 'ES', 'ET', 'EU', 'EV', 'EW', 'EX', 'EY', 'EZ');
        return $array[$key];
    }

    protected function column($key, $columnnum = 1)
    {
        return $this->column_str($key) . $columnnum;
    }

    /**
     * 导出Excel
     * @param type $list
     * @param type $params
     */
    public function export($data)
    {
        if (PHP_SAPI == 'cli') {
            exit('This example should only be run from a Web Browser');
        }

        $objPHPExcel = new \PHPExcel();
        //设置文件标题、描述、关键字
        $objPHPExcel->getProperties()
            ->setCreator('lmaster')
            ->setLastModifiedBy('lmaster')
            ->setTitle('The item by lmaster')
            ->setSubject('The item by lmaster')
            ->setDescription('Please contact Lmaster:407987823@qq.com')
            ->setKeywords('lmaster')
            ->setCategory('report file');
        $sheet = $objPHPExcel->setActiveSheetIndex(0);
        //getColumnDimension设置宽度（参数列号）
        $bodyColLen = count($data['body']['columns']);
        $rownum     = 1;
        if (isset($data['header'])) {
            foreach ($data['header'] as $k => $v) {
                $sheet->mergeCells($this->column(0, $rownum) . ':' . $this->column($bodyColLen - 1, $rownum));
                $sheet->setCellValue($this->column(0, $rownum), $v);
                $sheet->getStyle($this->column(0, $rownum))->applyFromArray($this->centerStyle);
                ++$rownum;
            }
        }
        //设置第一行
        foreach ($data['body']['columns'] as $key => $column) {
            $sheet->setCellValue($this->column($key, $rownum), $column['title']);
            $sheet->getStyle($this->column($key, $rownum))->applyFromArray($this->centerStyle);

            if (!(empty($column['width']))) {
                $sheet->getColumnDimension($this->column_str($key))->setWidth($column['width']);
            }

        }

        ++$rownum;
        //构建数据
        foreach ($data['body']['data'] as $row) {
            $i = 0;

            while ($i < $bodyColLen) {
                $value = ((isset($row[$data['body']['columns'][$i]['field']]) ? $row[$data['body']['columns'][$i]['field']] : ''));
                $sheet->setCellValue($this->column($i, $rownum), $value);
                ++$i;
            }

            ++$rownum;
        }

        $objPHPExcel->getActiveSheet()->setTitle($data['title']);
        $filename = urlencode($data['title'] . '-' . date('Y-m-d H:i', time()));
        ob_end_clean();
        header('Content-Type: application/octet-stream');
        header('Content-Disposition: attachment;filename="' . $data['title'] . '.xls"');
        header('Cache-Control: max-age=0');
        $writer = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $writer->save('php://output');
        exit();
    }

    // /**
    //  * 生成模板文件Excel
    //  * @param type $list
    //  * @param type $params
    //  */
    // public function temp($title, $columns = array())
    // {
    //     if (PHP_SAPI == 'cli') {
    //         exit('This example should only be run from a Web Browser');
    //     }

    //     require_once IA_ROOT . '/framework/library/phpobjPHPExcel/PHPExcel.php';
    //     $objPHPExcel = new PHPExcel();
    //     $objPHPExcel->getProperties()->setCreator('lmaster')->setLastModifiedBy('lmaster')->setTitle('The item by lmaster')->setSubject('The item by lmaster')->setDescription('Please contact Lmaster:407987823@qq.com')->setKeywords('lmaster')->setCategory('report file');
    //     $sheet  = $objPHPExcel->setActiveSheetIndex(0);
    //     $rownum = 1;

    //     foreach ($columns as $key => $column) {
    //         $sheet->setCellValue($this->column($key, $rownum), $column['title']);

    //         if (!(empty($column['width']))) {
    //             $sheet->getColumnDimension($this->column_str($key))->setWidth($column['width']);
    //         }

    //     }

    //     ++$rownum;
    //     $len = count($columns);
    //     $k   = 1;

    //     while ($k <= 5000) {
    //         $i = 0;

    //         while ($i < $len) {
    //             $sheet->setCellValue($this->column($i, $rownum), '');
    //             ++$i;
    //         }

    //         ++$rownum;
    //         ++$k;
    //     }

    //     $objPHPExcel->getActiveSheet()->setTitle($title);
    //     $filename = urlencode($title);
    //     header('Content-Type: application/octet-stream');
    //     header('Content-Disposition: attachment;filename="' . $filename . '.xls"');
    //     header('Cache-Control: max-age=0');
    //     $writer = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    //     $writer->save('php://output');
    //     exit();
    // }

    // public function import($excefile)
    // {
    //     global $_W;
    //     require_once IA_ROOT . '/framework/library/phpobjPHPExcel/PHPExcel.php';
    //     require_once IA_ROOT . '/framework/library/phpobjPHPExcel/PHPExcel/IOFactory.php';
    //     require_once IA_ROOT . '/framework/library/phpobjPHPExcel/PHPExcel/Reader/Excel5.php';
    //     $path = IA_ROOT . '/addons/ewei_shop/data/tmp/';

    //     if (!(is_dir($path))) {
    //         load()->func('file');
    //         mkdirs($path, '0777');
    //     }

    //     $filename = $_FILES[$excefile]['name'];
    //     $tmpname  = $_FILES[$excefile]['tmp_name'];

    //     if (empty($tmpname)) {
    //         message('请选择要上传的Excel文件!', '', 'error');
    //     }

    //     $ext = strtolower(pathinfo($filename, PATHINFO_EXTENSION));
    //     if (($ext != 'xlsx') && ($ext != 'xls')) {
    //         message('请上传 xls 或 xlsx 格式的Excel文件!', '', 'error');
    //     }

    //     $file       = time() . $_W['uniacid'] . '.' . $ext;
    //     $uploadfile = $path . $file;
    //     $result     = move_uploaded_file($tmpname, $uploadfile);

    //     if (!($result)) {
    //         message('上传Excel 文件失败, 请重新上传!', '', 'error');
    //     }

    //     $reader             = PHPExcel_IOFactory::createReader(($ext == 'xls' ? 'Excel5' : 'Excel2007'));
    //     $objPHPExcel        = $reader->load($uploadfile);
    //     $sheet              = $objPHPExcel->getActiveSheet();
    //     $highestRow         = $sheet->getHighestRow();
    //     $highestColumn      = $sheet->getHighestColumn();
    //     $highestColumnCount = PHPExcel_Cell::columnIndexFromString($highestColumn);
    //     $values             = array();
    //     $row                = 2;

    //     while ($row <= $highestRow) {
    //         $rowValue = array();
    //         $col      = 0;

    //         while ($col < $highestColumnCount) {
    //             $rowValue[] = $sheet->getCellByColumnAndRow($col, $row)->getValue();
    //             ++$col;
    //         }

    //         $values[] = $rowValue;
    //         ++$row;
    //     }

    //     return $values;
    // }
}
